#!/usr/bin/env python
# coding: utf-8

# # 阶段五模块四作业

# In[122]:


import numpy as np
import pandas as pd


# ## 要求1-3：数据加载

# In[123]:


DScoreBoy = pd.read_excel('./18级高一体测成绩汇总.xls',
              sheet_name= 0,
              header=0
              )
DScoreGirl = pd.read_excel('./18级高一体测成绩汇总.xls',
              sheet_name= 1,
              header=0
              )
DScoreStd = pd.read_excel('./体测成绩评分表.xls',
              sheet_name= 0,
              header=(0,1)
              )
display(DScoreBoy,DScoreGirl,DScoreStd)


# 空数据用字符0填充

# In[124]:


DScoreBoy.fillna(value='0')
DScoreGirl.fillna(value='0')
DScoreStd.fillna(value='0')


# ## 要求4：数据类型转换

# In[125]:


df =pd.Series(data=DScoreBoy['男1000米跑'].astype('str'))
x = 0
pfloat = 0.0
for i in range(0,len(df.index)):
    if '\''  in df[i]:
        x= int(df[i][0])
        if len(df[i]) ==4:
            pfloat = x +int(df[i][2:4])/60
        elif len(df[i]) ==3:
            pfloat = x +int(df[i][2])/6
    else:
        pfloat = int(df[i])/1
    DScoreBoy.loc[i,'男1000米跑']= round(pfloat,3)

df = pd.Series(data=DScoreStd['男1000米跑','成绩'])

for i in range(0,len(df.index)):
    x= int(df[i][0])
    pfloat = x +int(df[i][2:4])/60
    DScoreStd.loc[i,('男1000米跑','成绩')]= round(pfloat,3)

df = pd.Series(data=DScoreStd['女800米跑','成绩'])

for i in range(0,len(df.index)):
    x= int(df[i][0])
    pfloat = x +int(df[i][2:4])/60
    DScoreStd.loc[i,('女800米跑','成绩')]= round(pfloat,3)


# 所有数值统一为float

# In[126]:


# 男生成绩转换为浮点
dft1 = pd.DataFrame(DScoreBoy.iloc[:,0:2])
dft2 = pd.DataFrame(DScoreBoy.iloc[:,2:].astype('float'))
dfboy = pd.concat([dft1,dft2],axis=1)
# 女生成绩转换为浮点
dft1 = pd.DataFrame(DScoreGirl.iloc[:,0:2])
dft2 = pd.DataFrame(DScoreGirl.iloc[:,2:].astype('float'))
dfgirl = pd.concat([dft1,dft2],axis=1)

display(dfboy,dfgirl)


# 生成分数标准区间表

# In[127]:


dftemp = pd.Series(data= ['男肺活量','女肺活量','男50米跑','女50米跑',
                          '男体前屈','女体前屈','男跳远','女跳远',
                          '男引体','女仰卧','男1000米跑','女800米跑']
                  )
dfscore =pd.DataFrame(columns= pd.MultiIndex.from_product([['男肺活量','女肺活量','男50米跑','女50米跑',
                                                            '男体前屈','女体前屈','男跳远','女跳远',
                                                            '男引体','女仰卧','男1000米跑','女800米跑'],
                                                           ['成绩hi','成绩low','分数hi','分数low']]),
                     index= range(0,len(DScoreStd.index)-1))
for i in range(0,len(DScoreStd.index)-1):
    for j in range(0,len(dftemp)):
        dfscore.loc[i,(dftemp[j],'成绩hi')] = DScoreStd.loc[i,(dftemp[j],'成绩')]
        if pd.isna(DScoreStd.loc[i,(dftemp[j],'分数')] ) == True:
            dfscore.loc[i,(dftemp[j],'分数hi')] = (DScoreStd.loc[i+1,(dftemp[j],'分数')]+DScoreStd.loc[i-1,(dftemp[j],'分数')])/2
        else:
            dfscore.loc[i,(dftemp[j],'分数hi')] = DScoreStd.loc[i,(dftemp[j],'分数')]
        
        dfscore.loc[i,(dftemp[j],'成绩low')] = DScoreStd.loc[i+1,(dftemp[j],'成绩')]
        if pd.isna(DScoreStd.loc[i+1,(dftemp[j],'分数')] ) == True:
            dfscore.loc[i,(dftemp[j],'分数low')] = (DScoreStd.loc[i+2,(dftemp[j],'分数')]+DScoreStd.loc[i,(dftemp[j],'分数')])/2
        else:
            dfscore.loc[i,(dftemp[j],'分数low')] = DScoreStd.loc[i+1,(dftemp[j],'分数')]
        
dfscore = pd.DataFrame(data=dfscore.astype('float'))
dfscore


# ## 要求5、对体测成绩进行分数转换，跑步类（越小越好）；跳远、体前屈（越大越好）

# In[130]:


def FindScore(s_hi,s_low,r_hi,r_low,r_now):
    s_now = (((r_now-r_low)*(s_hi-s_low))/(r_hi-r_low))+s_low
    return round(s_now,2)
# 一次函数拟合


# In[131]:


def StrConcat(s1,s2):
    s=s1+s2
    return s
# 字符串合并


# 男生成绩转换

# In[132]:


dftemp = pd.Series(data= ['男肺活量','男50米跑','男体前屈','男跳远','男引体','男1000米跑'] )

for i in range(0,len(dftemp)):
    for j in range(0,len(dfboy.index)):
        for k in range(0,len(dfscore.index)):
            if (dftemp[i] == '男50米跑') or (dftemp[i] == '男1000米跑'):
                
                if dfboy.loc[j,dftemp[i]] <= dfscore.loc[k,(dftemp[i],'成绩hi')]:
                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数hi')],2)
                    break
                elif dfboy.loc[j,dftemp[i]] == dfscore.loc[k,(dftemp[i],'成绩low')]:
                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数low')],2)
                    break
                elif dfboy.loc[j,dftemp[i]] >= max(dfscore[dftemp[i],'成绩low']): #大于最低分成绩算最低分
                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = min(dfscore[dftemp[i],'分数low'])
                    break
                elif dfscore.loc[k,(dftemp[i],'成绩hi')] < dfboy.loc[j,dftemp[i]] < dfscore.loc[k,(dftemp[i],'成绩low')]:
                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = FindScore(s_hi=dfscore.loc[k,(dftemp[i],'分数hi')],
                                                                       s_low=dfscore.loc[k,(dftemp[i],'分数low')],
                                                                       r_hi=dfscore.loc[k,(dftemp[i],'成绩hi')],
                                                                       r_low=dfscore.loc[k,(dftemp[i],'成绩low')],
                                                                       r_now=dfboy.loc[j,dftemp[i]]
                                                                      )
                    break
                
            elif dftemp[i] == '男引体':  #特殊处理
                if pd.isna(dfscore.loc[k,(dftemp[i],'成绩hi')]):
                    continue
                elif dfboy.loc[j,dftemp[i]] <= min(dfscore[dftemp[i],'成绩low']): #小于最低分成绩算最低分
                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = min(dfscore[dftemp[i],'分数low'])
                    break
                elif dfboy.loc[j,dftemp[i]] == dfscore.loc[k,(dftemp[i],'成绩hi')]:
                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数hi')],2)
                    break
                
            else:
                if dfboy.loc[j,dftemp[i]] >= dfscore.loc[k,(dftemp[i],'成绩hi')]:
                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数hi')],2)
                    break
                elif dfboy.loc[j,dftemp[i]] == dfscore.loc[k,(dftemp[i],'成绩low')]:
                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数low')],2)
                    break
                elif dfboy.loc[j,dftemp[i]] <= min(dfscore[dftemp[i],'成绩low']): #小于最低分成绩算最低分
                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = min(dfscore[dftemp[i],'分数low'])
                    break
                elif dfscore.loc[k,(dftemp[i],'成绩hi')] > dfboy.loc[j,dftemp[i]] > dfscore.loc[k,(dftemp[i],'成绩low')]:
                    dfboy.loc[j,StrConcat(dftemp[i],'分数')] = FindScore(s_hi=dfscore.loc[k,(dftemp[i],'分数hi')],
                                                                       s_low=dfscore.loc[k,(dftemp[i],'分数low')],
                                                                       r_hi=dfscore.loc[k,(dftemp[i],'成绩hi')],
                                                                       r_low=dfscore.loc[k,(dftemp[i],'成绩low')],
                                                                       r_now=dfboy.loc[j,dftemp[i]]
                                                                      )
                    break
dfboy


# 女生成绩转换

# In[134]:


dftemp = pd.Series(data= ['女肺活量','女50米跑','女体前屈','女跳远','女仰卧','女800米跑'] )

for i in range(0,len(dftemp)):
    for j in range(0,len(dfgirl.index)):
        for k in range(0,len(dfscore.index)):
            if (dftemp[i] == '女50米跑') or (dftemp[i] == '女800米跑'):
                if dfgirl.loc[j,dftemp[i]] <= dfscore.loc[k,(dftemp[i],'成绩hi')]:
                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数hi')],2)
                    break
                elif dfgirl.loc[j,dftemp[i]] == dfscore.loc[k,(dftemp[i],'成绩low')]:
                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数low')],2)
                    break
                elif dfgirl.loc[j,dftemp[i]] >= max(dfscore[dftemp[i],'成绩low']): #大于最低分成绩算最低分
                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = min(dfscore[dftemp[i],'分数low'])
                    break
                elif dfscore.loc[k,(dftemp[i],'成绩hi')] < dfgirl.loc[j,dftemp[i]] < dfscore.loc[k,(dftemp[i],'成绩low')]:
                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = FindScore(s_hi=dfscore.loc[k,(dftemp[i],'分数hi')],
                                                                       s_low=dfscore.loc[k,(dftemp[i],'分数low')],
                                                                       r_hi=dfscore.loc[k,(dftemp[i],'成绩hi')],
                                                                       r_low=dfscore.loc[k,(dftemp[i],'成绩low')],
                                                                       r_now=dfgirl.loc[j,dftemp[i]]
                                                                      )
                    break
                
                          
            else:
                if dfgirl.loc[j,dftemp[i]] >= dfscore.loc[k,(dftemp[i],'成绩hi')]:
                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数hi')],2)
                    break
                elif dfgirl.loc[j,dftemp[i]] == dfscore.loc[k,(dftemp[i],'成绩low')]:
                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = round(dfscore.loc[k,(dftemp[i],'分数low')],2)
                    break
                elif dfgirl.loc[j,dftemp[i]] <= min(dfscore[dftemp[i],'成绩low']): #小于最低分成绩算最低分
                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = min(dfscore[dftemp[i],'分数low'])
                    break
                elif dfscore.loc[k,(dftemp[i],'成绩hi')] > dfgirl.loc[j,dftemp[i]] > dfscore.loc[k,(dftemp[i],'成绩low')]:
                    dfgirl.loc[j,StrConcat(dftemp[i],'分数')] = FindScore(s_hi=dfscore.loc[k,(dftemp[i],'分数hi')],
                                                                       s_low=dfscore.loc[k,(dftemp[i],'分数low')],
                                                                       r_hi=dfscore.loc[k,(dftemp[i],'成绩hi')],
                                                                       r_low=dfscore.loc[k,(dftemp[i],'成绩low')],
                                                                       r_now=dfgirl.loc[j,dftemp[i]] 
                                                                      )
                    break
dfgirl


# ## 最终结果输出

# In[136]:


a = [0,1,2,16,3,12,4,14,5,13,6,15,7,11,8,9,10]
newcol = np.array(a)
newcol


# In[139]:


dfboy= dfboy.take(newcol,axis= 1)
dfgirl= dfgirl.take(newcol,axis= 1)
display(dfboy,dfgirl)


# In[ ]:





# In[ ]:




